Re: [GENERAL] Two variable passed to PL/Function and on is NULL

At 15:44 +0300 on 14/06/1999, Stuart Rison wrote:


> i) Is it the case that if you pass to variables to a postgres function and
> one is NULL, the function cannot tell which one?
> ii) Is there a workaround of some kind (in particular in pl/pgsql)?
> [Currently I am copying the table into a temp table and updating all NULL
> values to a token value.]

I think you will do alright in pl/pgsql. Take for example the following
function which returns 1000 when its argument is null:

create function null1000( int4 ) returns int4 as '
DECLARE
  the_arg alias for $1;
BEGIN
  IF the_arg IS NULL THEN
     RETURN 1000;
  ELSE
     RETURN the_arg;
  END IF;
END;
' language 'plpgsql';

I tested it on the following table:

testing=> select * from test1;
nm
--
 4
 8

16
32

(6 rows)

And this is the result I got:

testing=> select null1000( nm ) from test1;
null1000
--------
       4
       8
    1000
      16
      32
    1000
(6 rows)

The problem arises if you try to pass a literal NULL to the function:

testing=> select null1000( NULL );
ERROR:  typeidTypeRelid: Invalid type - oid = 0

This is because the NULL doesn't have a type of int4. I am not even sure
this is a bug. In any case, it should work alright for normal NULLS. In the
same vein, you can ask whether the arguments in your function are null and
return your boolean properly.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma